package com.hy.convenient.tool.xlsx;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.util.StringUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 文件导出工具
 *
 * @author hdy
 * @date 2019年08月27日 16:00
 */
public class ExportUtils<T> {
    /**
     * 表格导出 方法 可以参照下拉框的方式添加样式的写法
     *
     * @param map             表头名字和属性名的对应
     * @param list            数据
     * @param fileName        文件名
     * @param sheetName       表空间名
     * @param dorpDownHashMap 下拉框 key对应 map的value
     *                        HashMap<String, String[]> dorpDownHashMap = new HashMap<>();
     *                        String[] dropDown = {"**", "--", "++", "//"};
     *                        dorpDownHashMap.put("age", dropDown3);
     * @param response
     */
    public void exportData(LinkedHashMap<String, String> map, List<T> list, String fileName, String sheetName
            , Map<String, String[]> dorpDownHashMap, HttpServletResponse response, HttpServletRequest request) {
        if (StringUtils.isEmpty(fileName)) {
//            new PsCoreException("文件名不能为空");
        }
        if (StringUtils.isEmpty(sheetName)) {
            sheetName = "sheet1";
        }
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(sheetName);
            DataValidationHelper helper = sheet.getDataValidationHelper();
//        标题行
            HSSFRow rowTitle = sheet.createRow(0);
            int size = 0;
            int dataSize = list.size();
            for (String value : map.keySet()) {
                rowTitle.createCell(size).setCellValue(value);
                size++;
                if(dorpDownHashMap==null){

                    continue;
                }
                String[] str = dorpDownHashMap.getOrDefault(map.get(value), null);
                if (str != null) {
                    CellRangeAddressList addressList = new CellRangeAddressList(1, dataSize > 500 ? dataSize : 500, size, size);
                    DataValidationConstraint constraint = helper.createExplicitListConstraint(str);
                    DataValidation dataValidation = helper.createValidation(constraint, addressList);
                    dataValidation.setSuppressDropDownArrow(false);
                    sheet.addValidationData(dataValidation);
                }
            }
            for (int i = 0; i < dataSize; i++) {
                T t = list.get(i);
                Map<String, String> maps = BeanUtils.describe(t);
                HSSFRow row = sheet.createRow(i + 1);
                int k = 0;
                for (String value : map.values()) {
                    row.createCell(k).setCellValue(maps.getOrDefault(value, ""));
                    k++;
                }
            }
            Date date = new Date();
            SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
            fileName ="E:\\宏远文件\\"+formatter.format(date)+".xls";
            outPut(fileName,wb);
//当前是需要导出到本地菜屏蔽
            //            outPut(response, request, fileName, wb);
        } catch (IllegalAccessException e) {
//            new PsCoreException("导出模板异常");
        } catch (InvocationTargetException e) {
//            new PsCoreException("导出模板异常");
        } catch (NoSuchMethodException e) {
//            new PsCoreException("导出模板异常");
        }
    }

    public void exportDataForLocation(LinkedHashMap<String, String> map, List<T> list, String fileName,String filepath) {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("sheet1");
            DataValidationHelper helper = sheet.getDataValidationHelper();
//        标题行
            HSSFRow rowTitle = sheet.createRow(0);
            int size = 0;
            int dataSize = list.size();
            for (String value : map.keySet()) {
                rowTitle.createCell(size).setCellValue(value);
                size++;
            }
            for (int i = 0; i < dataSize; i++) {
                T t = list.get(i);
                Map<String, String> maps = BeanUtils.describe(t);
                HSSFRow row = sheet.createRow(i + 1);
                int k = 0;
                for (String value : map.values()) {
                    row.createCell(k).setCellValue(maps.getOrDefault(value, ""));
                    k++;
                }
            }
            fileName =filepath+fileName+".xls";
            outPut(fileName,wb);
        } catch (IllegalAccessException e) {
//            new PsCoreException("导出模板异常");
        } catch (InvocationTargetException e) {
//            new PsCoreException("导出模板异常");
        } catch (NoSuchMethodException e) {
//            new PsCoreException("导出模板异常");
        }
    }
    /**
     * 导出采用模板形式 不需要才添加样式
     *
     * @param map      字段和表头名的对应
     * @param list     导出数据
     * @param fileName 文件名
     * @param response
     * @param request
     * @param path     传入的文件名 文件需要放在资源下的 static/下载目录(配置)/   之传入文件名例如 WYProperty.xls
     */
    public byte[] exportTemplate(Map<String, String> map, List<T> list, String fileName,
                               HttpServletResponse response, HttpServletRequest request, String path) {
        try {
            Resource resource = new ClassPathResource("static/" +"/".trim()+path.trim());
            File file = resource.getFile();
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow rowHeader = sheet.getRow(0);
            int rowNum = rowHeader.getPhysicalNumberOfCells();
            LinkedHashMap<String, String> map1 = new LinkedHashMap<>();
            for (int i = 0; i < rowNum; i++) {
                String value = rowHeader.getCell(i).getStringCellValue();
                map1.put(value, map.getOrDefault(value, null));
                map.remove(value);
            }
            if (map.size() > 0) {
//                throw new PsCoreRuntimeException("导出数据表头不合适");
            }
            map1.remove(null);
            int dataSize = list.size();
            for (int i = 0; i < dataSize; i++) {
                T aa = list.get(i);
                Map<String, String> maps = BeanUtils.describe(aa);
                HSSFRow row = sheet.createRow(i + 1);
                int k = 0;
                for (String value : map1.values()) {
                    row.createCell(k).setCellValue(maps.getOrDefault(value, ""));
                    k++;
                }
            }
            outPut(response, request, fileName, wb);
            return wb.getBytes();
        } catch (IOException | InvocationTargetException | IllegalAccessException | NoSuchMethodException e) {
//            throw new PsCoreRuntimeException("导出异常");
        }
        return null;
    }
    /**
     * 导出采用模板形式 不需要才添加样式
     *
     * @param map      字段和表头名的对应
     * @param list     导出数据
     * @param fileName 文件名
     * @param response
     * @param request
     */
    public byte[] exportHttpServletResponse(Map<String, String> map, List<T> list, String fileName,
                                 HttpServletResponse response, HttpServletRequest request) {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow rowHeader = sheet.getRow(0);
            int rowNum = rowHeader.getPhysicalNumberOfCells();
            LinkedHashMap<String, String> map1 = new LinkedHashMap<>();
            for (int i = 0; i < rowNum; i++) {
                String value = rowHeader.getCell(i).getStringCellValue();
                map1.put(value, map.getOrDefault(value, null));
                map.remove(value);
            }
            if (map.size() > 0) {
//                throw new PsCoreRuntimeException("导出数据表头不合适");
            }
            map1.remove(null);
            int dataSize = list.size();
            for (int i = 0; i < dataSize; i++) {
                T aa = list.get(i);
                Map<String, String> maps = BeanUtils.describe(aa);
                HSSFRow row = sheet.createRow(i + 1);
                int k = 0;
                for (String value : map1.values()) {
                    row.createCell(k).setCellValue(maps.getOrDefault(value, ""));
                    k++;
                }
            }
            outPut(response, request, fileName, wb);
            return wb.getBytes();
        } catch (InvocationTargetException | IllegalAccessException | NoSuchMethodException e) {
//            throw new PsCoreRuntimeException("导出异常");
        }
        return null;
    }

    private void outPut(String fileName, HSSFWorkbook wb) {
        OutputStream ou = null;
        try {
            ou = new FileOutputStream(fileName);
            wb.write(ou);
            ou.flush();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                ou.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }
    /**
     * 用于输出
     *
     * @param response
     * @param request
     * @param fileName
     * @param wb
     */
    private void outPut(HttpServletResponse response, HttpServletRequest request, String fileName, HSSFWorkbook wb) {
        String userAgent = request.getHeader("User-Agent");
        //IE浏览器处理
        try {
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                // 非IE浏览器的处理：
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            }
        }
        catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }  catch (IOException e) {
            e.printStackTrace();
        }

        //response.setCharacterEncoding("UTF-8");
        response.addHeader("Charset", "UTF-8");
        response.addHeader("Content-Type", "application/force-download");
//        response.addHeader("Content-Type", "application/vnd.ms-excel");
        response.addHeader("Content-Type", "application/x-www-form-urlencoded");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//        response.setContentType("APPLICATION/OCTET-STREAM");//返回格式为流
        response.addHeader("Content-disposition", "attachment;filename=" +  fileName);
        try (ServletOutputStream outputStream = response.getOutputStream()){
            wb.write(outputStream);
            //outputStream.write(wb.getBytes());
        } catch (IOException e) {
//            throw new PsCoreRuntimeException("导出模板异常");
        }
    }
}
